VolTimeCalculator

V8 Message Definiton

This table allows custom span risk calculations based on either user or SR supplied input values.

METADATA

Attribute	Value
Topic	5030-srse-calculators
MLink Token	SystemData
Product	SRAnalytics
accessType	SELECT,UPDATE,INSERT,DELETE

Table Definition

Field	Type	Key	Default Value	Comment
ticker_at	enum - AssetType	PRI	'None'
ticker_ts	enum - TickerSrc	PRI	'None'
ticker_tk	VARCHAR(12)	PRI	''
secType	enum - SpdrKeyType	PRI	'None'
endDate	DATE	PRI	'1900-01-01'	period end date CST
userName	VARCHAR(24)	PRI	''
endTime	TIME(6)		'16:00:00'	period end time CST
nowDttm	DATETIME(6)		'2000-01-01'	period start time CST
nowSrc	enum - FieldSrc		'Default'	default is clock time when selecting
timeMetric	enum - TimeMetric		'D252'
timeMetricSrc	enum - FieldSrc		'Default'
holidayCalendar	enum - CalendarCode		'NYSE'
holidayCalendarSrc	enum - FieldSrc		'Default'
years	DOUBLE		0	volatility years from nowDttm to endDttm using TradingCalendar SR NMS TradingVolatility Calendar
years365	DOUBLE		0
partialDays	INT		0	number of partial trading days halfdays between endpoints
holidayDays	INT		0	number of holidays days that would otherwise be trading days between endpoints
completeDays	INT		0	number of complete trading days between endpoints
trdMinutes	INT		0	number of market trading minutes between endpoints
clkMinutes	INT		0	number of nonmarket trading minutes between endpoints
trdMeasure	DOUBLE		0	volatility years per trdMinute
clkMeasure	DOUBLE		0	volatility years per clkMinute
error	VARCHAR(32)		''	calculation error
timestamp	DATETIME(6)		'2000-01-01'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
ticker_tk	1
ticker_at	2
ticker_ts	3
secType	4
endDate	5
userName	6

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgVolTimeCalculator` (
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `secType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None',
    `endDate` DATE NOT NULL DEFAULT '1900-01-01' COMMENT 'period end date (CST)',
    `userName` VARCHAR(24) NOT NULL DEFAULT '',
    `endTime` TIME(6) NOT NULL DEFAULT '16:00:00' COMMENT 'period end time (CST)',
    `nowDttm` DATETIME(6) NOT NULL DEFAULT '2000-01-01' COMMENT 'period start time (CST)',
    `nowSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default' COMMENT 'default is clock time when selecting',
    `timeMetric` ENUM('None','D252','D365','SPX','WK1','WK2','WK3','WK4') NOT NULL DEFAULT 'D252',
    `timeMetricSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default',
    `holidayCalendar` ENUM('None','NYSE','EUREX','BXE','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','CEDX','ICEFE') NOT NULL DEFAULT 'NYSE',
    `holidayCalendarSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default',
    `years` DOUBLE NOT NULL DEFAULT 0 COMMENT 'volatility years from nowDttm to endDttm using TradingCalendar (SR NMS Trading/Volatility Calendar)',
    `years365` DOUBLE NOT NULL DEFAULT 0,
    `partialDays` INT NOT NULL DEFAULT 0 COMMENT 'number of partial trading days (half-days) between endpoints.',
    `holidayDays` INT NOT NULL DEFAULT 0 COMMENT 'number of holidays (days that would otherwise be trading days) between endpoints.',
    `completeDays` INT NOT NULL DEFAULT 0 COMMENT 'number of complete trading days between endpoints.',
    `trdMinutes` INT NOT NULL DEFAULT 0 COMMENT 'number of market trading minutes between endpoints.',
    `clkMinutes` INT NOT NULL DEFAULT 0 COMMENT 'number of non-market trading minutes between endpoints.',
    `trdMeasure` DOUBLE NOT NULL DEFAULT 0 COMMENT 'volatility years per trdMinute',
    `clkMeasure` DOUBLE NOT NULL DEFAULT 0 COMMENT 'volatility years per clkMinute',
    `error` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'calculation error',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '2000-01-01',
    PRIMARY KEY USING HASH (`ticker_tk`,`ticker_at`,`ticker_ts`,`secType`,`endDate`,`userName`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='This table allows custom span risk calculations based on either user or SR supplied input values.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `secType`,
    `endDate`,
    `userName`,
    `endTime`,
    `nowDttm`,
    `nowSrc`,
    `timeMetric`,
    `timeMetricSrc`,
    `holidayCalendar`,
    `holidayCalendarSrc`,
    `years`,
    `years365`,
    `partialDays`,
    `holidayDays`,
    `completeDays`,
    `trdMinutes`,
    `clkMinutes`,
    `trdMeasure`,
    `clkMeasure`,
    `error`,
    `timestamp`
FROM `SRAnalytics`.`MsgVolTimeCalculator`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk'
  AND
    /* Replace with a ENUM('None','Stock','Future','Option','MLeg') */ 
    `secType` = 'None'
  AND
    /* Replace with a DATE */
    `endDate` = '2022-01-01'
  AND
    /* Replace with a VARCHAR(24) */ 
    `userName` = 'Example_userName';

UPDATE TABLE EXAMPLE QUERY

UPDATE `SRAnalytics`.`MsgVolTimeCalculator` 
SET
    /* Replace with a TIME(6) */
    `endTime` = '12:34:56.000000',
    /* Replace with a DATETIME(6) */
    `nowDttm` = '2022-01-01 12:34:56.000000',
    /* Replace with a ENUM('Default','User') */
    `nowSrc` = 'Default',
    /* Replace with a ENUM('None','D252','D365','SPX','WK1','WK2','WK3','WK4') */
    `timeMetric` = 'D252',
    /* Replace with a ENUM('Default','User') */
    `timeMetricSrc` = 'Default',
    /* Replace with a ENUM('None','NYSE','EUREX','BXE','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','CEDX','ICEFE') */
    `holidayCalendar` = 'NYSE',
    /* Replace with a ENUM('Default','User') */
    `holidayCalendarSrc` = 'Default',
    /* Replace with a DOUBLE */
    `years` = 4.56,
    /* Replace with a DOUBLE */
    `years365` = 4.56,
    /* Replace with a INT */
    `partialDays` = 5,
    /* Replace with a INT */
    `holidayDays` = 5,
    /* Replace with a INT */
    `completeDays` = 5,
    /* Replace with a INT */ 
    `trdMinutes` = 5,
    /* Replace with a INT */ 
    `clkMinutes` = 5,
    /* Replace with a DOUBLE */ 
    `trdMeasure` = 4.56,
    /* Replace with a DOUBLE */ 
    `clkMeasure` = 4.56,
    /* Replace with a VARCHAR(32) */
    `error` = 'Example_error',
    /* Replace with a DATETIME(6) */
    `timestamp` = '2022-01-01 12:34:56.000000'
WHERE
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk'
  AND
    /* Replace with a ENUM('None','Stock','Future','Option','MLeg') */ 
    `secType` = 'None'
  AND
    /* Replace with a DATE */
    `endDate` = '2022-01-01'
  AND
    /* Replace with a VARCHAR(24) */ 
    `userName` = 'Example_userName';

INSERT TABLE EXAMPLE QUERY

INSERT INTO `SRAnalytics`.`MsgVolTimeCalculator`(
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_at`,
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ticker_ts`,
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk`,
    /* Replace with a ENUM('None','Stock','Future','Option','MLeg') */ 
    `secType`,
    /* Replace with a DATE */
    `endDate`,
    /* Replace with a VARCHAR(24) */ 
    `userName`,
    /* Replace with a TIME(6) */
    `endTime`,
    /* Replace with a DATETIME(6) */
    `nowDttm`,
    /* Replace with a ENUM('Default','User') */
    `nowSrc`,
    /* Replace with a ENUM('None','D252','D365','SPX','WK1','WK2','WK3','WK4') */
    `timeMetric`,
    /* Replace with a ENUM('Default','User') */
    `timeMetricSrc`,
    /* Replace with a ENUM('None','NYSE','EUREX','BXE','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','CEDX','ICEFE') */
    `holidayCalendar`,
    /* Replace with a ENUM('Default','User') */
    `holidayCalendarSrc`,
    /* Replace with a DOUBLE */
    `years`,
    /* Replace with a DOUBLE */
    `years365`,
    /* Replace with a INT */
    `partialDays`,
    /* Replace with a INT */
    `holidayDays`,
    /* Replace with a INT */
    `completeDays`,
    /* Replace with a INT */ 
    `trdMinutes`,
    /* Replace with a INT */ 
    `clkMinutes`,
    /* Replace with a DOUBLE */ 
    `trdMeasure`,
    /* Replace with a DOUBLE */ 
    `clkMeasure`,
    /* Replace with a VARCHAR(32) */
    `error`,
    /* Replace with a DATETIME(6) */
    `timestamp`
) 
VALUES(
    'None',
    'None',
    'Example_ticker_tk',
    'None',
    '2022-01-01',
    'Example_userName',
    '12:34:56.000000',
    '2022-01-01 12:34:56.000000',
    'Default',
    'D252',
    'Default',
    'NYSE',
    'Default',
    4.56,
    4.56,
    5,
    5,
    5,
    5,
    5,
    4.56,
    4.56,
    'Example_error',
    '2022-01-01 12:34:56.000000'
);

DELETE TABLE EXAMPLE QUERY

DELETE FROM `SRAnalytics`.`MsgVolTimeCalculator` 
WHERE
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk'
  AND
    /* Replace with a ENUM('None','Stock','Future','Option','MLeg') */ 
    `secType` = 'None'
  AND
    /* Replace with a DATE */
    `endDate` = '2022-01-01'
  AND
    /* Replace with a VARCHAR(24) */ 
    `userName` = 'Example_userName';

Doc Columns Query

SELECT * FROM SRAnalytics.doccolumns WHERE TABLE_NAME='VolTimeCalculator' ORDER BY ordinal_position ASC;